package wei.qiang.ye.springbootjpa.repository;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;
import wei.qiang.ye.springbootjpa.entity.UserDO;

public interface UserRepository03 extends PagingAndSortingRepository<UserDO, Integer> {
    @Query("SELECT u FROM UserDO u WHERE u.username = ?1")
    UserDO findByUsername01(String username);

    @Query("SELECT u FROM UserDO u WHERE u.username = :username")
    UserDO findByUsername02(@Param("username") String username);

    /**
     * nativeQuery=true表示使用原生sql (from 表名)，而不是jpsl (from 实体名)
     * @param username
     * @return
     */
    @Query(value = "SELECT * FROM users u WHERE u.username = :username", nativeQuery = true)
    UserDO findByUsername03(@Param("username") String username);

    @Query("UPDATE UserDO  u SET u.username = :username WHERE u.id = :id")
    @Modifying
    int updateUsernameById(@Param("id")Integer id, @Param("username")String username);

    /**
     * 分页查询
     * @param lastname
     * @param pageable
     * @return
     */
    @Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1", // value 属性，编写查询分页列表的 SQL 。
            countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1", // countQuery 属性，编写记录总数的 SQL 。
            nativeQuery = true)
    Page<UserDO> findByLastname(String lastname, Pageable pageable);
}
